﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net.Mail;
using System.Data;
using System.Data.SQLite;

namespace PTCScheduler
{
    //Class to handle pushing out emails to teachers.
    class EmailAutomation
    {
        //String to hold the teacher emails. TO: of emails.
        private String To;
        public String TO
        {
            set 
            { 
                To = value; 
            }
            get
            {
                return To;
            }
        }//Gets and sets for to.

        //String to hold the from email. Will be final.
        private const String From = "ihs.ptc.reminder@gmail.com";
        private const String pwd = "Mn06Ad07Mb08Ab09";

        //String to hold body of email
        private String bodyInitialEmail = "";
        private String bodyReminderEmail = "";
        private String bodyUpdateEmail = "";
        private String bodyDeleteEmail = "";

        //Set up the mail message object for email.
        private MailMessage message;

        //SQLite Connection
        private SQLiteConnection conn;

        //SQLite Connection String
        private String connString = SystemQueries.dbConnString();

        //Object constructor.
        public EmailAutomation()
        {
        }

        //Method to handle sending emails.
        public void sendEmail()
        {
            //Create message object
            message = new MailMessage(From, TO);

            //Create message subject.
            message.Subject = "Scheduled Parent Teacher Conference";

            //Create body of message.
            message.Body = bodyInitialEmail;

            //Body is not html
            message.IsBodyHtml = true;
            
            //Create smtpclient object to connect to google servers.
            SmtpClient client = new SmtpClient("smtp.gmail.com", 587);
            
            //Enable ssl
            client.EnableSsl = true;

            //Credentials for the sender
            client.Credentials = new System.Net.NetworkCredential(From, pwd);

            //Catches failed recipient exception and attempts to resend.
            try
            {
                //Send the email.
                client.Send(message);
            }
            catch (SmtpFailedRecipientException ex)
            {
                //Retrieve status code.
                SmtpStatusCode statusCode = ex.StatusCode;

                //If server is busy, unavailable, or unexpected failure. Wait 2 seconds and try again. 
                if (statusCode == SmtpStatusCode.MailboxBusy ||statusCode == SmtpStatusCode.MailboxUnavailable ||statusCode == SmtpStatusCode.TransactionFailed)
               {
                    // wait 5 seconds, try a second time
                    System.Threading.Thread.Sleep(5000);
                    client.Send(message);
                }
                else
                {
                    throw;

                }
          }
          finally
          {
            //Dipose message.
            message.Dispose();
            //Dispose of client.
            client.Dispose();
          } 
        }

        //Method to send reminder email.
        public void sendReminderEmail()
        {
            //Create message object
            message = new MailMessage(From, TO);

            //Create message subject.
            message.Subject = "Reminder - Parent Teacher Conference";

            //Create body of message.
            message.Body = bodyReminderEmail;

            //Body is not html
            message.IsBodyHtml = true;
            
            //Create smtpclient object to connect to google servers.
            SmtpClient client = new SmtpClient("smtp.gmail.com", 587);

            //Enable ssl
            client.EnableSsl = true;

            //Credentials for the sender
            client.Credentials = new System.Net.NetworkCredential(From, pwd);

            //Catches failed recipient exception and attempts to resend.
            try
            {
                //Send the email.
                client.Send(message);
            }
            catch (SmtpFailedRecipientException ex)
            {
                //Retrieve status code.
                SmtpStatusCode statusCode = ex.StatusCode;

                //If server is busy, unavailable, or unexpected failure. Wait 2 seconds and try again. 
                if (statusCode == SmtpStatusCode.MailboxBusy || statusCode == SmtpStatusCode.MailboxUnavailable || statusCode == SmtpStatusCode.TransactionFailed)
                {
                    // wait 5 seconds, try a second time
                    System.Threading.Thread.Sleep(5000);
                    client.Send(message);
                }
                else
                {
                    throw;

                }
            }
            finally
            {
                //Dipose message.
                message.Dispose();
                //Dispose of client.
                client.Dispose();
            } 
        }

        //Method to send reminder email.
        public void sendUpdateEmail()
        {
            //Create message object
            message = new MailMessage(From, TO);

            //Create message subject.
            message.Subject = "Updated Parent Teacher Conference";

            //Create body of message.
            message.Body = bodyUpdateEmail;

            //Body is not html
            message.IsBodyHtml = true;

            //Create smtpclient object to connect to google servers.
            SmtpClient client = new SmtpClient("smtp.gmail.com", 587);

            //Enable ssl
            client.EnableSsl = true;

            //Credentials for the sender
            client.Credentials = new System.Net.NetworkCredential(From, pwd);

            //Catches failed recipient exception and attempts to resend.
            try
            {
                //Send the email.
                client.Send(message);
            }
            catch (SmtpFailedRecipientException ex)
            {
                //Retrieve status code.
                SmtpStatusCode statusCode = ex.StatusCode;

                //If server is busy, unavailable, or unexpected failure. Wait 2 seconds and try again. 
                if (statusCode == SmtpStatusCode.MailboxBusy || statusCode == SmtpStatusCode.MailboxUnavailable || statusCode == SmtpStatusCode.TransactionFailed)
                {
                    // wait 5 seconds, try a second time
                    System.Threading.Thread.Sleep(5000);
                    client.Send(message);
                }
                else
                {
                    throw;

                }
            }
            finally
            {
                //Dipose message.
                message.Dispose();
                //Dispose of client.
                client.Dispose();
            } 
        }

        //Method to send reminder email.
        public void sendCancelledEmail()
        {
            //Create message object
            message = new MailMessage(From, TO);

            //Create message subject.
            message.Subject = "Cancelled Parent Teacher Conference";

            //Create body of message.
            message.Body = bodyDeleteEmail;

            //Body is not html
            message.IsBodyHtml = true;

            //Create smtpclient object to connect to google servers.
            SmtpClient client = new SmtpClient("smtp.gmail.com", 587);

            //Enable ssl
            client.EnableSsl = true;

            //Credentials for the sender
            client.Credentials = new System.Net.NetworkCredential(From, pwd);

            //Catches failed recipient exception and attempts to resend.
            try
            {
                //Send the email.
                client.Send(message);
            }
            catch (SmtpFailedRecipientException ex)
            {
                //Retrieve status code.
                SmtpStatusCode statusCode = ex.StatusCode;

                //If server is busy, unavailable, or unexpected failure. Wait 2 seconds and try again. 
                if (statusCode == SmtpStatusCode.MailboxBusy || statusCode == SmtpStatusCode.MailboxUnavailable || statusCode == SmtpStatusCode.TransactionFailed)
                {
                    // wait 5 seconds, try a second time
                    System.Threading.Thread.Sleep(5000);
                    client.Send(message);
                }
                else
                {
                    throw;

                }
            }
            finally
            {
                //Dipose message.
                message.Dispose();
                //Dispose of client.
                client.Dispose();
            } 
        }

        //Method to build body of email based off of email details from conference.
        public void buildEmailBody(String teacherName, String details)
        {
            bodyInitialEmail = @"Dear Mr./Ms." + teacherName.Substring(0,teacherName.IndexOf(",")) + ", <br>" + "<br>" +
                                   "This is a confirmation email to inform that you have been scheduled for a parent teacher conference. Conference details are listed below: <br><br>" +
                                     details + "<br><br>" + "<b>Please DO NOT respond to this email. Contact the attendance/counselor administrative assistants with any questions or concerns.</b>";
        }

        //Method to build body of email based off of email details from conference.
        public void buildReminderEmailBody(String teacherName, String details)
        {
            bodyReminderEmail = @"Dear Mr./Ms." + teacherName.Substring(0, teacherName.IndexOf(",")) + ", <br>" + "<br>" +
                                   "This is a friendly reminder that you have been scheduled for a parent teacher conference. Conference details are listed below: <br><br>" +
                                     details + "<br><br>" + "<b>Please DO NOT respond to this email. Contact the attendance/counselor administrative assistants with any questions or concerns.</b>";
        }

        //Method to build body of email based off of email details from conference.
        public void buildUpdateEmailBody(String teacherName, String details)
        {
            bodyUpdateEmail = @"Dear Mr./Ms." + teacherName.Substring(0, teacherName.IndexOf(",")) + ", <br>" + "<br>" +
                                   "This is an email to inform you that the previously scheduled parent teacher conference details have <b>changed</b>. New conference details are listed below: <br><br>" +
                                     details + "<br><br>" + "<b>Please DO NOT respond to this email. Contact the attendance/counselor administrative assistants with any questions or concerns.</b>";
        }

        //Method to build body of cancelled conference.
        public void buildDeleteEmailBody(String teacherName, String details)
        {
            bodyDeleteEmail = @"Dear Mr./Ms." + teacherName.Substring(0, teacherName.IndexOf(",")) + ", <br>" + "<br>" +
                                   "This is an email to inform you that the previously scheduled parent teacher conference has been <b>cancelled</b>. Cancelled conference details are listed below: <br><br>" +
                                     details + "<br><br>" + "<b>Please DO NOT respond to this email. Contact the attendance/counselor administrative assistants with any questions or concerns.</b>";
        }

        //Method to build the reminder email details. 
        public String buildReminderEmailDetailsString(String CounselorNeeded, String Date, String Time, String Location,
            String CounselorName, String StudentID)
        {
            if (CounselorNeeded == "NO")
            {
                return @"<b>Parent-Teacher Conference Details</b>" + "<br>" + "<b>-------------------------------------------</b>" + "<br>" +
                "- Date: " + formatDate(Date) + "<br>" +
                    "- Time: " + Time + "<br>" +
                    "- Location: " + Location + "<br>" +
                    "- Student (ID): " + StudentID + "<br>";
            }

            return @"<b>Parent-Teacher Conference Details</b>" + "<br>" + "<b>---------------------------------------------</b>" + "<br>" +
                "- Date: " + formatDate(Date) + "<br>" +
                    "- Time: " + Time + "<br>" +
                    "- Location: " + Location + "<br>" +
                    "- Counselor: " + CounselorName + "<br>" +
                    "- Student (ID): " + StudentID + "<br>";
        }

        //Method to build the reminder email details. 
        public String buildUpdateEmailDetailsString(String CounselorNeeded, String Date, String Time, String Location,
            String CounselorName, String StudentID)
        {
            if (CounselorNeeded == "NO")
            {
                return @"<b>Parent-Teacher Conference Details</b>" + "<br>" + "<b>-------------------------------------------</b>" + "<br>" +
                "- Date: " + formatDate(Date) + "<br>" +
                    "- Time: " + Time + "<br>" +
                    "- Location: " + Location + "<br>" +
                    "- Student (ID): " + StudentID + "<br>";
            }

            return @"<b>Parent-Teacher Conference Details</b>" + "<br>" + "<b>---------------------------------------------</b>" + "<br>" +
                "- Date: " + formatDate(Date) + "<br>" +
                    "- Time: " + Time + "<br>" +
                    "- Location: " + Location + "<br>" +
                    "- Counselor: " + CounselorName + "<br>" +
                    "- Student (ID): " + StudentID + "<br>";
        }

        //Method to build the delete email details. 
        public String buildDeleteEmailDetailsString(String Date, String Time, String Location,String StudentID)
        {
            return @"<b>Parent-Teacher Conference Details</b>" + "<br>" + "<b>---------------------------------------------</b>" + "<br>" +
                "- Date: " + formatDate(Date) + "<br>" +
                    "- Time: " + Time + "<br>" +
                    "- Location: " + Location + "<br>" +
                    "- Student (ID): " + StudentID + "<br>";
        }
        
        //Format date to a more detailed style.
        private String formatDate(String date)
        {
            DateTime dtDate = DateTime.Parse(date);

            return dtDate.ToString("D");
        }

        //Method to retrieve teacher email.
        public void getTeacherEmail(int teacherID)
        {
            //Connect to database
            conn = new SQLiteConnection(connString);

            //Open connection.
            conn.Open();

            //Retrieve the teacher query string from the SystemQueries class and set the
            //teacher combobox contents to teacher names.
            String sql = SystemQueries.teacherEmailQueryString(teacherID);

            //SQLite command to retrieve counselor id
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;

            //Execute scalar to retrieve and set the value of email
            Object cEmail = cmd.ExecuteScalar();

            //Convert to addres to email.
            TO = Convert.ToString(cEmail);

            //Close connection.
            conn.Close();
        }
    }
}
